﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.ComponentModel;
using System.Collections;

    [DataObject(true)]
    public class MentorReport
    {
        private const string CONNECTION_STRING_NAME = "ApplicationConnectionString";

        public string getMentorViewsSql()
        {
            return GET_MENTOR_VIEWS;
        }
        public string getPathwayViewsSql()
        {
            return GET_PATHWAY_VIEWS;
        }
        public string getFocusViewsSql()
        {
            return GET_FOCUS_VIEWS;
        }
        private const string GET_MENTOR_VIEWS = "SELECT "
                                              + "m.mentor_id AS 'id', "
                                              + "m.first_name AS 'first name', "
                                              + "m.last_name AS 'last name', "
                                              + "(SELECT count(action_performed_id) "
                                              + "FROM lgd_mentor_student_actions "
                                              + "WHERE action_performed_id = 'V' "
                                              + "AND date_time BETWEEN @minDate and @maxDate "
                                              + "AND mentor_id = m.mentor_id) as num_visits, "
                                              + "(SELECT count(action_performed_id) "
                                              + "FROM lgd_mentor_student_actions  "
                                              + "WHERE  action_performed_id = 'E' "
                                              + "AND date_time BETWEEN @minDate and @maxDate "
                                              + "AND mentor_id = m.mentor_id) as num_emails "
                                              + "FROM mentor m, lgd_mentor_student_actions l "
                                              + "WHERE m.deletion_date IS null "
                                              + "AND m.mentor_id = l.mentor_id  "
                                              + "AND l.date_time BETWEEN @minDate and @maxDate "
                                              + "GROUP BY m.mentor_id "
                                              + "ORDER BY num_emails DESC, num_visits DESC ";

        private const string GET_PATHWAY_VIEWS = "SELECT "
                                               + "p.pathway_id AS 'id', "
                                               + "p.pathway_name AS 'pathway', "
                                               + "  (SELECT count(action_performed_id) "
                                               + "      FROM lgd_mentor_student_actions l, "
                                               + "      mentor m "
                                               + "      WHERE l.action_performed_id = 'V' "
                                               + "      AND l.date_time BETWEEN @minDate and @maxDate "
                                               + "      AND l.mentor_id = m.mentor_id "
                                               + "      AND p.pathway_id = m.pathway_id) as num_visits, "
                                               + "  (SELECT count(action_performed_id) "
                                               + "      FROM lgd_mentor_student_actions l,  "
                                               + "      mentor m "
                                               + "      WHERE  l.action_performed_id = 'E' "
                                               + "      AND l.date_time BETWEEN @minDate and @maxDate "
                                               + "      AND l.mentor_id = m.mentor_id "
                                               + "      AND p.pathway_id = m.pathway_id) as num_emails "
                                               + "FROM lkup_pathway p, mentor m, lgd_mentor_student_actions l "
                                               + "WHERE m.mentor_id = l.mentor_id  "
                                               + "AND m.pathway_id = p.pathway_id  "
                                               + "AND l.date_time BETWEEN @minDate and @maxDate "
                                               + "GROUP BY p.pathway_id "
                                               + "ORDER BY num_emails DESC, num_visits DESC ";


        private const string GET_FOCUS_VIEWS = "SELECT "
                                            + "f.focus_id AS 'id', "
                                            + "f.focus_name AS 'focus', "
                                            + "(SELECT count(action_performed_id) "
                                            + "FROM lgd_mentor_student_actions l, "
                                            + "mentor m, "
                                            + "lkup_pathway p "
                                            + "WHERE l.action_performed_id = 'V' "
                                            + "AND l.date_time BETWEEN @minDate and @maxDate "
                                            + "AND l.mentor_id = m.mentor_id "
                                            + "AND p.pathway_id = m.pathway_id "
                                            + "AND p.focus_id = f.focus_id) as num_visits, "
                                            + "(SELECT count(action_performed_id) "
                                            + "FROM lgd_mentor_student_actions l,  "
                                            + "mentor m, "
                                            + "lkup_pathway p "
                                            + "WHERE  l.action_performed_id = 'E' "
                                            + "AND l.date_time BETWEEN @minDate and @maxDate "
                                            + "AND l.mentor_id = m.mentor_id "
                                            + "AND p.pathway_id = m.pathway_id "
                                            + "AND p.focus_id = f.focus_id) as num_emails "
                                            + "FROM lkup_focus_area f, lkup_pathway p, mentor m, lgd_mentor_student_actions l "
                                            + "WHERE m.mentor_id = l.mentor_id  "
                                            + "AND m.pathway_id = p.pathway_id  "
                                            + "AND l.date_time BETWEEN @minDate and @maxDate "
                                            + "AND f.focus_id = p.focus_id "
                                            + "GROUP BY p.pathway_id "
                                            + "ORDER BY num_emails DESC, num_visits DESC ";


        private string getConnectionString()
        {
            //return  "server=localhost;user id=root;password=root;database=clearfield_high;pooling=true;";
            //return "server=www.freesql.org;user id=CHSgroup;password=clearfield;database=clearfield_high;pooling=true;";
            return ConfigurationManager.ConnectionStrings[CONNECTION_STRING_NAME].ConnectionString;
        }

        private static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings
            [CONNECTION_STRING_NAME].ConnectionString;
        }

    }